import time
import pymysql
import datetime
import paramiko
import requests
import crcmod.predefined
from dbutils.pooled_db import PooledDB
from make_voltage_err import create_err_voltage_error
from make_collect_data import create_24h_dy_data_for_calc

ssh = paramiko.SSHClient()
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())


def deal_time(str_time):
    """
    根据标准格式时间转化为报文所需的格式
    :param str_time:
    :return:
    """
    year = int(str_time[:4])
    month = int(str_time[5:7])
    day = int(str_time[8:10])

    hour = int(str_time[11:13])
    minute = int(str_time[14:16])
    second = int(str_time[17:20])

    # print(year)
    # print(month)
    # print(day)
    # print(hour)
    # print(minute)
    # print(second)

    hex_year = hex(year)[2:].rjust(4, '0')
    hex_month = hex(month)[2:].rjust(2, '0')
    hex_day = hex(day)[2:].rjust(2, '0')
    hex_hour = hex(hour)[2:].rjust(2, '0')
    hex_minute = hex(minute)[2:].rjust(2, '0')
    hex_second = hex(second)[2:].rjust(2, '0')

    # print(hex_year)
    # print(hex_month)
    # print(hex_day)
    # print(hex_hour)
    # print(hex_minute)
    # print(hex_second)

    return "1C {} {} {} {} {} {} {}".format(hex_year[0:2], hex_year[2:4], hex_month, hex_day, hex_hour, hex_minute,
                                            hex_second)


def get_crc_16_x25_check(string):
    """
    根据报文生成校验帧（HCS,FCS）
    :param string:
    :return:
    """
    # 采用封号好的crcmod库进行crc-16-X25校验
    crc16_func = crcmod.predefined.mkCrcFun("x-25")
    data = bytearray.fromhex(string)
    check_data = hex(crc16_func(data))[2:].zfill(4)
    # print(check_data)
    crc_1 = check_data[0:2]
    crc_2 = check_data[2:4]
    check_code = crc_2 + " " + crc_1
    # print(crc_1)
    # print(crc_2)
    # print(check_code)
    return check_code


def wait_time():
    """
    等待整点15分钟
    :return:
    """
    now_time = datetime.datetime.now()
    minute_time = datetime.datetime.now().minute
    print(now_time)
    print(minute_time)
    certain_minute = [0, 15, 30, 45]
    print(certain_minute)
    for i in range(60 * 1):
        if minute_time in certain_minute:
            break
        else:
            time.sleep(1)
            minute_time = datetime.datetime.now().minute
            now_time = datetime.datetime.now()
            print("wait_time")
            print(minute_time)
            print(now_time)


def get_previous_15min_time():
    """
    获取上一个15分钟的整15分钟时间
    :return:
    """
    now = datetime.datetime.now()
    # print(now)
    minutes = now.minute

    # 向下取整到最近的5的倍数
    minutes = minutes - minutes % 15
    # 秒数为1
    last_time = now.replace(minute=minutes, second=0, microsecond=0)
    last_time_15 = last_time - datetime.timedelta(minutes=15)

    end = last_time.strftime("%Y-%m-%d %H:%M:%S")
    start = last_time_15.strftime("%Y-%m-%d %H:%M:%S")
    return start, end


def get_all_meter_addr(ip):
    """
    获取所有的表计地址
    :param ip:
    :return:
    """
    POOL = PooledDB(creator=pymysql, maxconnections=6, mincached=2, maxcached=5, maxshared=3, blocking=True,
                    maxusage=None, setsession=[], ping=0, host=ip, port=3306, user='oetsky', password="Oetsky@123",
                    database='cvt_client_prod', charset='utf8')

    conn = POOL.connection()
    cursor = conn.cursor()
    # 获取数据库当前时间的上一分钟时间, 未恢复事件
    select_sql = "SELECT meter_addr FROM addr_voltage_phase;"
    cursor.execute(select_sql)
    datas = cursor.fetchall()
    cursor.close()
    conn.close()
    list_meter_addr = []
    for data in datas:
        list_meter_addr.append(data[0])
    return list_meter_addr


def get_collect_err(ip, meter_addr, collect_time):
    """
    获取采集和评估
    :param ip:
    :param meter_addr:
    :param collect_time:
    :return:
    """
    calc_time = datetime.datetime.strptime(collect_time, "%Y-%m-%d %H:%M:%S").replace(
        hour=0, minute=0, second=0) - datetime.timedelta(minutes=1)

    POOL = PooledDB(creator=pymysql, maxconnections=6, mincached=2, maxcached=5, maxshared=3, blocking=True,
                    maxusage=None, setsession=[], ping=0, host=ip, port=3306, user='oetsky', password="Oetsky@123",
                    database='cvt_client_prod', charset='utf8')

    conn = POOL.connection()
    cursor = conn.cursor()

    # 获取线路num和相序
    select_sql = "SELECT channel_num, phase_seq FROM addr_voltage_phase WHERE meter_addr='{}';".format(meter_addr)
    cursor.execute(select_sql)
    datas = cursor.fetchall()
    channel_num = datas[0][0]
    phase_seq = datas[0][1]

    # 获取采集数据
    select_sql = "SELECT * FROM da_voltage_data_temp WHERE channel_num={} and collect_time='{}';".format(
        channel_num, collect_time)
    cursor.execute(select_sql)
    data_collect = cursor.fetchall()

    # 获取评估数据
    select_sql = "SELECT * FROM err_voltage_error WHERE channel_num={} and calculate_time='{}';".format(
        channel_num, calc_time)
    cursor.execute(select_sql)
    data_calc = cursor.fetchall()

    # 关闭游标和数据库连接
    cursor.close()
    conn.close()

    # 获取sql频率幅值相位
    if data_collect:
        aa_fundamental_frequency = data_collect[0][6]
        aa_fundamental_amplitude = data_collect[0][7]
        aa_fundamental_phase = data_collect[0][10]

        bb_fundamental_frequency = data_collect[0][13]
        bb_fundamental_amplitude = data_collect[0][14]
        bb_fundamental_phase = data_collect[0][17]

        cc_fundamental_frequency = data_collect[0][20]
        cc_fundamental_amplitude = data_collect[0][21]
        cc_fundamental_phase = data_collect[0][24]
    else:
        aa_fundamental_frequency = None
        aa_fundamental_amplitude = None
        aa_fundamental_phase = None

        bb_fundamental_frequency = None
        bb_fundamental_amplitude = None
        bb_fundamental_phase = None

        cc_fundamental_frequency = None
        cc_fundamental_amplitude = None
        cc_fundamental_phase = None

    # 获取sql比差角差
    if data_calc:
        aa_ratio_error = data_calc[0][3]
        bb_ratio_error = data_calc[0][4]
        cc_ratio_error = data_calc[0][5]

        aa_angle_error = data_calc[0][9]
        bb_angle_error = data_calc[0][10]
        cc_angle_error = data_calc[0][11]
        calculate_status = data_calc[0][21]
    else:
        aa_ratio_error = None
        bb_ratio_error = None
        cc_ratio_error = None

        aa_angle_error = None
        bb_angle_error = None
        cc_angle_error = None
        calculate_status = None

    # print(aa_fundamental_frequency)
    # print(aa_fundamental_amplitude)
    # print(aa_fundamental_phase)
    #
    # print(bb_fundamental_frequency)
    # print(bb_fundamental_amplitude)
    # print(bb_fundamental_phase)
    #
    # print(cc_fundamental_frequency)
    # print(cc_fundamental_amplitude)
    # print(cc_fundamental_phase)
    #
    # print(aa_ratio_error)
    # print(bb_ratio_error)
    # print(cc_ratio_error)
    #
    # print(aa_angle_error)
    # print(bb_angle_error)
    # print(cc_angle_error)

    if phase_seq == "A":
        return aa_fundamental_frequency, aa_fundamental_amplitude, aa_fundamental_phase, aa_ratio_error, aa_angle_error, calculate_status
    elif phase_seq == "B":
        return bb_fundamental_frequency, bb_fundamental_amplitude, bb_fundamental_phase, bb_ratio_error, bb_angle_error, calculate_status
    elif phase_seq == "C":
        return cc_fundamental_frequency, cc_fundamental_amplitude, cc_fundamental_phase, cc_ratio_error, cc_angle_error, calculate_status
    else:
        assert False, "错误的相序：{}".format(phase_seq)


def get_latest_collect(ip, meter_addr):
    """
    获取站端指定表计地址最新的采集数据
    :param ip:
    :param meter_addr:
    :return:
    """
    POOL = PooledDB(creator=pymysql, maxconnections=6, mincached=2, maxcached=5, maxshared=3, blocking=True,
                    maxusage=None, setsession=[], ping=0, host=ip, port=3306, user='oetsky', password="Oetsky@123",
                    database='cvt_client_prod', charset='utf8')

    conn = POOL.connection()
    cursor = conn.cursor()

    # 获取线路num和相序
    select_sql = "SELECT channel_num, phase_seq FROM addr_voltage_phase WHERE meter_addr='{}';".format(meter_addr)
    cursor.execute(select_sql)
    datas = cursor.fetchall()
    channel_num = datas[0][0]
    phase_seq = datas[0][1]

    # 获取采集数据
    select_sql = "SELECT * FROM da_voltage_data_temp WHERE channel_num={} ORDER BY collect_time DESC LIMIT 1;".format(
        channel_num)
    cursor.execute(select_sql)
    data_collect = cursor.fetchall()

    # 关闭游标和数据库连接
    cursor.close()
    conn.close()

    # 获取sql频率幅值相位
    if data_collect:
        aa_fundamental_frequency = data_collect[0][6]
        aa_fundamental_amplitude = data_collect[0][7]
        aa_fundamental_phase = data_collect[0][10]

        bb_fundamental_frequency = data_collect[0][13]
        bb_fundamental_amplitude = data_collect[0][14]
        bb_fundamental_phase = data_collect[0][17]

        cc_fundamental_frequency = data_collect[0][20]
        cc_fundamental_amplitude = data_collect[0][21]
        cc_fundamental_phase = data_collect[0][24]
    else:
        aa_fundamental_frequency = None
        aa_fundamental_amplitude = None
        aa_fundamental_phase = None

        bb_fundamental_frequency = None
        bb_fundamental_amplitude = None
        bb_fundamental_phase = None

        cc_fundamental_frequency = None
        cc_fundamental_amplitude = None
        cc_fundamental_phase = None

    # print(aa_fundamental_frequency)
    # print(aa_fundamental_amplitude)
    # print(aa_fundamental_phase)
    #
    # print(bb_fundamental_frequency)
    # print(bb_fundamental_amplitude)
    # print(bb_fundamental_phase)
    #
    # print(cc_fundamental_frequency)
    # print(cc_fundamental_amplitude)
    # print(cc_fundamental_phase)

    if phase_seq == "A":
        return aa_fundamental_frequency, aa_fundamental_amplitude, aa_fundamental_phase
    elif phase_seq == "B":
        return bb_fundamental_frequency, bb_fundamental_amplitude, bb_fundamental_phase
    elif phase_seq == "C":
        return cc_fundamental_frequency, cc_fundamental_amplitude, cc_fundamental_phase
    else:
        assert False, "错误的相序：{}".format(phase_seq)


def get_before_yesterday_calc(ip, meter_addr, modify_stand_time):
    """
    获取站端前天的计算数据
    :param ip:
    :param meter_addr:
    :return:
    """
    before_yesterday_time = modify_stand_time.replace(hour=0, minute=0, second=0) - datetime.timedelta(
        days=1, minutes=1)
    POOL = PooledDB(creator=pymysql, maxconnections=6, mincached=2, maxcached=5, maxshared=3, blocking=True,
                    maxusage=None, setsession=[], ping=0, host=ip, port=3306, user='oetsky', password="Oetsky@123",
                    database='cvt_client_prod', charset='utf8')

    conn = POOL.connection()
    cursor = conn.cursor()

    # 获取线路num和相序
    select_sql = "SELECT channel_num, phase_seq FROM addr_voltage_phase WHERE meter_addr='{}';".format(meter_addr)
    cursor.execute(select_sql)
    datas = cursor.fetchall()
    channel_num = datas[0][0]
    phase_seq = datas[0][1]

    # 获取评估数据
    select_sql = "SELECT * FROM err_voltage_error WHERE channel_num={} and calculate_time='{}';".format(
        channel_num, before_yesterday_time)
    cursor.execute(select_sql)
    data_calc = cursor.fetchall()

    # 关闭游标和数据库连接
    cursor.close()
    conn.close()

    # 获取sql比差角差
    if data_calc:
        aa_ratio_error = data_calc[0][3]
        bb_ratio_error = data_calc[0][4]
        cc_ratio_error = data_calc[0][5]

        aa_angle_error = data_calc[0][9]
        bb_angle_error = data_calc[0][10]
        cc_angle_error = data_calc[0][11]
        calculate_status = data_calc[0][21]
    else:
        aa_ratio_error = None
        bb_ratio_error = None
        cc_ratio_error = None

        aa_angle_error = None
        bb_angle_error = None
        cc_angle_error = None
        calculate_status = None

    # print(aa_fundamental_frequency)
    # print(aa_fundamental_amplitude)
    # print(aa_fundamental_phase)
    #
    # print(bb_fundamental_frequency)
    # print(bb_fundamental_amplitude)
    # print(bb_fundamental_phase)
    #
    # print(cc_fundamental_frequency)
    # print(cc_fundamental_amplitude)
    # print(cc_fundamental_phase)
    #
    # print(aa_ratio_error)
    # print(bb_ratio_error)
    # print(cc_ratio_error)
    #
    # print(aa_angle_error)
    # print(bb_angle_error)
    # print(cc_angle_error)

    if phase_seq == "A":
        return aa_ratio_error, aa_angle_error, calculate_status
    elif phase_seq == "B":
        return bb_ratio_error, bb_angle_error, calculate_status
    elif phase_seq == "C":
        return cc_ratio_error, cc_angle_error, calculate_status
    else:
        assert False, "错误的相序：{}".format(phase_seq)


def get_yesterday_calc(ip, meter_addr, modify_stand_time):
    """
    获取站端昨天的计算数据
    :param ip:
    :param meter_addr:
    :return:
    """
    before_yesterday_time = modify_stand_time.replace(hour=0, minute=0, second=0) - datetime.timedelta(minutes=1)
    POOL = PooledDB(creator=pymysql, maxconnections=6, mincached=2, maxcached=5, maxshared=3, blocking=True,
                    maxusage=None, setsession=[], ping=0, host=ip, port=3306, user='oetsky', password="Oetsky@123",
                    database='cvt_client_prod', charset='utf8')

    conn = POOL.connection()
    cursor = conn.cursor()

    # 获取线路num和相序
    select_sql = "SELECT channel_num, phase_seq FROM addr_voltage_phase WHERE meter_addr='{}';".format(meter_addr)
    cursor.execute(select_sql)
    datas = cursor.fetchall()
    channel_num = datas[0][0]
    phase_seq = datas[0][1]

    # 获取评估数据
    select_sql = "SELECT * FROM err_voltage_error WHERE channel_num={} and calculate_time='{}';".format(
        channel_num, before_yesterday_time)
    cursor.execute(select_sql)
    data_calc = cursor.fetchall()

    # 关闭游标和数据库连接
    cursor.close()
    conn.close()

    # 获取sql比差角差
    if data_calc:
        aa_ratio_error = data_calc[0][3]
        bb_ratio_error = data_calc[0][4]
        cc_ratio_error = data_calc[0][5]

        aa_angle_error = data_calc[0][9]
        bb_angle_error = data_calc[0][10]
        cc_angle_error = data_calc[0][11]
        calculate_status = data_calc[0][21]
    else:
        aa_ratio_error = None
        bb_ratio_error = None
        cc_ratio_error = None

        aa_angle_error = None
        bb_angle_error = None
        cc_angle_error = None
        calculate_status = None

    # print(aa_fundamental_frequency)
    # print(aa_fundamental_amplitude)
    # print(aa_fundamental_phase)
    #
    # print(bb_fundamental_frequency)
    # print(bb_fundamental_amplitude)
    # print(bb_fundamental_phase)
    #
    # print(cc_fundamental_frequency)
    # print(cc_fundamental_amplitude)
    # print(cc_fundamental_phase)
    #
    # print(aa_ratio_error)
    # print(bb_ratio_error)
    # print(cc_ratio_error)
    #
    # print(aa_angle_error)
    # print(bb_angle_error)
    # print(cc_angle_error)

    if phase_seq == "A":
        return aa_ratio_error, aa_angle_error, calculate_status
    elif phase_seq == "B":
        return bb_ratio_error, bb_angle_error, calculate_status
    elif phase_seq == "C":
        return cc_ratio_error, cc_angle_error, calculate_status
    else:
        assert False, "错误的相序：{}".format(phase_seq)


def get_stand_time(ip):
    """
    获取站端时间
    :param ip:
    :return:
    """
    # 创建SSH客户端
    client = paramiko.SSHClient()
    client.set_missing_host_key_policy(paramiko.AutoAddPolicy())

    # 连接SSH服务器
    client.connect(hostname=ip, port=10088, username="oetsky", password="oetsky@87942")

    # 执行date命令
    stdin, stdout, stderr = client.exec_command('date +"%Y-%m-%d %H:%M:%S"')

    # 获取命令输出
    stand_time = stdout.read().decode('utf-8').strip()
    stand_datetime = datetime.datetime.strptime(stand_time, "%Y-%m-%d %H:%M:%S")
    print(stand_time)
    # print(stand_datetime)

    # 关闭连接
    client.close()
    return stand_time, stand_datetime


def get_pc_time():
    """
    获取PC时间
    :return:
    """
    # PC当前时间
    now = datetime.datetime.now()
    pc_time = now.strftime("%Y-%m-%d %H:%M:%S")
    # print(pc_time)
    pc_datetime = datetime.datetime.strptime(pc_time, "%Y-%m-%d %H:%M:%S")
    # print(pc_datetime)
    return pc_time, pc_datetime


def ssh_exe_cmd(cmd):
    """
    执行单条cmd
    :param cmd:
    :return:
    """
    global ssh
    stdin, stdout, stderr = ssh.exec_command(cmd)
    output_str = stdout.read().decode('utf-8')
    return output_str


def channel_exe_cmd(channel, cmd, end_flag="# "):
    """
    执行多条cmd
    :param channel:
    :param cmd:
    :param end_flag:
    :return:
    """
    channel.send(cmd)
    channel.send("\n")
    time.sleep(0.1)
    buff = ''
    start_time = time.time()
    while end_flag not in buff:
        assert time.time() - start_time < 260, "cmd命令超时260秒:{}".format(cmd)
        resp = channel.recv(9999)
        # 出现异常字符时忽略
        buff += resp.decode('utf-8', errors='ignore')
        # print(buff)
    return buff


def url_is_accessible(url):
    """
    判断url是否能访问
    :param url:
    :return:
    """
    try:
        response = requests.get(url)
        return response.status_code == 200
    except requests.exceptions.RequestException:
        return False


def modify_centos_date_restart(ip, centos_date):
    """
    修改站端时间
    :param ip:
    :param centos_date:
    :return:
    """
    for _ in range(5):
        ssh.connect(hostname=ip, port=10088, username="oetsky", password="oetsky@87942")
        ssh_exe_cmd('sudo /home/oetsky/data/bin/chpwd')
        output_str = ssh_exe_cmd("sudo /home/oetsky/data/bin/chpwd --debug")
        # print(output_str)
        su_password = output_str[-7:]
        # print("sudo 密码：{}".format(su_password))
        channel = ssh.invoke_shell()
        time.sleep(0.1)

        channel_exe_cmd(channel, "su", end_flag='密码：')
        channel_exe_cmd(channel, su_password)

        channel_exe_cmd(channel, 'date -s "{}"'.format(centos_date))
        print('date -s "{}"'.format(centos_date))
        channel_exe_cmd(channel, 'sync')
        channel_exe_cmd(channel, "sh /opt/app/oetsky/data/scripts/cvt_client_run.sh restart",
                        "---> cvt_client_run.sh:43")
        time.sleep(15)
        flag = url_is_accessible("http://{}:8082/login".format(ip))
        if flag:
            print("重启站端服务结束")
            break
        else:
            print("restart之后页面无法访问")
            time.sleep(60)


def modify_centos_current_date_restart(ip):
    """
    修改站端时间为当前PC时间
    :param ip:
    :return:
    """
    pc_time, _ = get_pc_time()
    modify_centos_date_restart(ip, pc_time)


def execute_sql(ip, update_sql):
    """
    执行指定sql语句
    :param ip:
    :param update_sql:
    :return:
    """
    POOL = PooledDB(creator=pymysql, maxconnections=6, mincached=2, maxcached=5, maxshared=3, blocking=True,
                    maxusage=None, setsession=[], ping=0, host=ip, port=3306, user='oetsky', password='Oetsky@123',
                    database='cvt_client_prod', charset='utf8')
    conn = POOL.connection()
    cursor = conn.cursor()
    # print(update_sql)
    try:
        cursor.execute(update_sql)
        # print("已更新数据表")
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cursor.close()
        conn.close()


def assert_data(float_digit, data_left, data_right):
    """
    断言左右数据相等，按照位数四舍五入并保留有效数字
    :param float_digit:
    :param data_left:
    :param data_right:
    :return:
    """
    assert "{:.%sf}" % (str(float_digit)).format(round(data_left, float_digit)) == "{:.%sf}" % (
        str(float_digit)).format(round(data_right, float_digit))


def make_collect_err_data(ip, collect_forward, collect_day, err_forward, err_day):
    """
    制作2v2的采集和误差数据
    :param ip:
    :param collect_forward:采集数据提前天数
    :param collect_day:采集天数
    :param err_forward:计算数据提前天数，0：提前一天
    :param err_day:计算数据天数
    :return:
    """
    pre_date = (datetime.date.today() + datetime.timedelta(days=collect_forward)).strftime("%Y-%m-%d %H:%M:%S")
    v_330_level = 8  # 330kV电压等级对应的字典键值
    v_330_count = 0  # 330kV电压等级线路数
    v_330_ty = False  # 330kV电压等级同源标志  True  False
    v_330_fl = False  # 330kV电压等级分列标志
    v_330_td = False  # 330kV电压等级停电标志

    v_220_level = 5  # 220kV电压等级对应的字典键值
    v_220_count = 2  # 220kV电压等级线路数
    v_220_ty = False  # 220kV电压等级同源标志  True  False
    v_220_fl = False  # 220kV电压等级分列标志
    v_220_td = False  # 220kV电压等级停电标志

    v_110_level = 4  # 110kV电压等级对应的字典键值
    v_110_count = 2  # 110kV电压等级线路数
    v_110_ty = False  # 110kV电压等级同源标志  True  False
    v_110_fl = False  # 110kV电压等级分列标志
    v_110_td = False  # 110kV电压等级停电标志

    v_10_level = 3  # 10kV电压等级对应的字典键值
    v_10_count = 0  # 10kV电压等级线路数
    v_10_ty = False  # 10kV电压等级同源标志  True  False
    v_10_fl = False  # 10kV电压等级分列标志
    v_10_td = False  # 10kV电压等级停电标志

    v_arg_list = [v_330_level, v_330_count, v_330_ty, v_330_fl, v_330_td, v_220_level, v_220_count, v_220_ty, v_220_fl,
                  v_220_td, v_110_level, v_110_count, v_110_ty, v_110_fl, v_110_td, v_10_level, v_10_count, v_10_ty,
                  v_10_fl, v_10_td]
    create_24h_dy_data_for_calc(ip, p_date=pre_date, v_list=v_arg_list, time=collect_day)

    # 2、插入前天、昨天、今天的计算数据
    create_err_voltage_error(ip, (datetime.datetime.now() + datetime.timedelta(days=err_forward)).strftime('%Y-%m-%d'),
                             time=err_day)


if __name__ == '__main__':
    # str_time = "2024-07-05 15:00:00"
    # res = deal_time(str_time)
    # print(res)

    # # string = "40 00 43 05 01 00 15 00 00 43 00"
    # string = "40 00 43 05 01 00 15 00 00 43 00"
    # check_code = get_crc_16_x25_check(string)
    # print(check_code)

    # last_time = get_previous_15min_time()
    # print(last_time)

    # list_meter_addr = get_all_meter_addr("192.168.253.113")
    # print(list_meter_addr)

    # meter_addr = "111111110001"
    # collect_time = "2024-07-19 00:00:00"
    # data = get_collect_err("192.168.253.113", "111111110001", "2024-07-19 00:00:00")
    # print(data)

    # get_stand_time("192.168.253.113")

    # get_pc_time()

    # start_time = time.time()
    # modify_centos_date_restart("192.168.253.113", "2024-07-22 00:00:00")
    # print(time.time() - start_time)

    start, end = get_previous_15min_time()
    print(start, end)
